import time
from socket import *
import os,sys
from threading import *
from queue import Queue
from pymysql import *
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from web_socket_test import get_data
from weixin import main
#实现功能模块
HOST = "localhost"
PORT = 5000
ADDR = (HOST,PORT)
q = Queue()

# 连接数据库
class Mysqlpython:
    def __init__(self, host="39.106.159.161",
                 user="root",
                 password="123456",
                 port=3306,
                 charset="utf8"):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset

    def open(self):
        '''
        连接数据库
        '''
        self.db = connect(host=self.host,
                          user=self.user,
                          password=self.password,
                          port=self.port,
                          charset=self.charset)
        self.cur = self.db.cursor()

    def close(self):
        '''
        关闭数据库
        '''
        self.cur.close()
        self.db.close()

    def get_data(self):
        datas = q.get()
        data = datas.split(",")
        equipment_name = data[1] #设备名称
        warning_integer = data[6] #报警种类
        equipment_type = data[5]  #报警状态
        date = data[2] #时间
        year = date.split('-')[0]
        if date.split('-')[1][0] == '0':
            month = date.split('-')[1][1]
        else:
            month =date.split('-')[1]
        number_flashes = data[4] #2s闪电数量

        print(equipment_name, warning_integer, equipment_type, date, number_flashes,year,month)
        try:
            self.open()
            use_database = "use test;"
            self.cur.execute(use_database)
            sql = "select YJ_equipment_ID from s1_yj_equipment_info_table where YJ_equipment_name='%s'" % (equipment_name)
            customer_id = "select Customers_Employees_ID from s1_yj_equipment_info_table where YJ_equipment_name='%s'" % (equipment_name)
            self.cur.execute(customer_id)
            customer_id = self.cur.fetchone()
            Wechat = "select YJ_equipment_ID from s1_customers_company_employees_info_table where Customers_Employees_ID='%s'" % (customer_id)
            self.cur.execute(Wechat)
            Wechat = self.cur.fetchone()

            self.cur.execute(sql)

            result = self.cur.fetchone()
            if result ==None:
                equipment_ID =0
            print(result)
            equipment_ID =result[0]
            if int(warning_integer) > 0:
                get_data(datas)
                if Wechat:
                    main(Wechat)
        except Exception as e:
            print(e)
        self.close()
        self.insert_info(data, datas, equipment_name, equipment_ID, equipment_type, date, warning_integer, number_flashes,year,month)

    def insert_info(self, data, datas, equipment_name, equipment_ID, equipment_type, date, warning_integer, number_flashes,year,month):
        equipment_sql = "insert into s1_id_yj_equipment_operation_rec(YJ_equipment_operation_rec, YJ_Equipment_ID, YJ_equipment_record_type,YJ_equipment_record_datetime) values('%s','%s','%s','%s');" % (
        datas, equipment_ID, equipment_type, date)
        warning_sql = "insert into s1_id_yj_equipment_monthly_warning_recs_table(Warning_recs,YJ_Equipment_ID,Date,Warning_recs_type,Warning_flags_integer,Number_flashes,Year,Month) values('%s','%s','%s','%s','%s','%s','%s','%s');" % (
        datas,equipment_ID,date,equipment_type,warning_integer,number_flashes,year,month)
        use_database = "use test;"
        # print(data)
        try:
            self.open()
            self.cur.execute(use_database)
            if int(data[5]) >0:
                self.cur.execute(equipment_sql)
                print('************')
                self.cur.execute(warning_sql)

            else:
                self.cur.execute(equipment_sql)
                print("---------------------")
            self.db.commit()
        except Exception as e :
            self.db.rollback()
            print("出现错误,已回滚",e)

        self.close()

class socket_sql:
    #发送邮件
    def send_email(self):
        # 第三方 SMTP 服务
        mail_host = "smtp.qq.com"  # 设置服务器
        mail_user = "1447968723"  # 用户名
        mail_pass = "vczppeaqrvymjhfj"  # 口令

        sender = '1447968723@qq.com'
        receivers = ['1447968723@qq.com']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱

        message = MIMEText('DATA数据接收接收错误', 'plain', 'utf-8')
        message['From'] = Header("OWD", 'utf-8')
        message['To'] = Header("DATA数据", 'utf-8')

        subject = 'Python SMTP 邮件测试'
        message['Subject'] = Header(subject, 'utf-8')

        try:
            smtpObj = smtplib.SMTP()
            smtpObj.connect(mail_host, 25)  # 25 为 SMTP 端口号
            smtpObj.login(mail_user, mail_pass)
            smtpObj.sendmail(sender, receivers, message.as_string())
            print("邮件发送成功")
        except smtplib.SMTPException:
            print("Error: 无法发送邮件")


    #客户端处理函数
    def handler(self,connfd):
        print("Connect from",connfd.getpeername())
        while True:
            try:
                data = connfd.recv(1024).decode()
                # time.sleep(1)
                if (not data) or data=='\n':
                    break
                q.put(data)
                connfd.send(b'OK')
                # print(data)
                sql = Mysqlpython()
                sql.get_data()
            except Exception as e :
                self.send_email()
                print(e)
                break
        connfd.close()

    def run(self):
        s = socket(AF_INET, SOCK_STREAM)
        # s.setsockopt(SOL_SOCKET, SO_REUSEADDR, 1)
        s.bind(ADDR)
        s.listen(5)
        while True:
            try:
                connfd, addr = s.accept()
            except KeyboardInterrupt:
                s.close()
                sys.exit("服务器退出")
            except Exception as e:
                print(e)
                continue
            t = Thread(target=self.handler, args=(connfd,))
            t.setDaemon(True)
            t.start()



socket_sql = socket_sql()
socket_sql.run()
